﻿using System;
using System.Data;
using System.Data.Common;
using System.Text;
using Besture.Common;
using Besture.Model;

namespace Besture.Dal
{
    public partial class RoomPriceDal : BaseDataAccess
    {

        public int GetModelByInRoomId(string iRoomId)
        {
            try
            { 
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append(string.Format("SELECT * FROM dbo.TBRoomPrice WHERE RoomID in({0}) ", iRoomId));
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0) return 1; 

                
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return -1;
        }

        public RoomPriceModel GetModelByRoomId(int iRoomId,int iPrice)
        {
            try
            {
                //string sWhere = "AND Price=0";
                //if (iPrice>0)
                //{
                //    sWhere = "";
                //}

                StringBuilder selectSql = new StringBuilder();
                selectSql.Append(string.Format("SELECT TOP 1 * FROM dbo.TBRoomPrice WHERE RoomID={0} ORDER BY UpdateTime DESC", iRoomId));
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0)
                    return ModelConvertHelper<RoomPriceModel>.ConvertToModelList(dt)[0];
                else return null;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        public RoomPriceModel GetModelByRoomIdAndRoomTime(int iRoomId, DateTime dRoomTime)
        {
            try
            {

                StringBuilder selectSql = new StringBuilder();
                selectSql.Append(string.Format("select * from TBRoomPrice where RoomTime='{0}' and RoomID={1} ", dRoomTime, iRoomId));
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0)
                    return ModelConvertHelper<RoomPriceModel>.ConvertToModelList(dt)[0];
                else return null;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }


        /// <summary>
        /// 获取详细信息
        /// </summary>
        /// <param name="id">主键值</param>
        /// <returns></returns>
        public RoomPriceModel GetModel(int id)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append("SELECT * FROM TBRoomPrice");
                selectSql.Append(" WHERE ");
                selectSql.Append(" ID=@ID ");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.Int32, id);
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0)
                    return ModelConvertHelper<RoomPriceModel>.ConvertToModelList(dt)[0];
                else return null;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 获取全部数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable()
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append("SELECT * FROM TBRoomPrice ORDER BY ID DESC ");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                return dt;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(RoomPriceModel model)
        {
            var identityId = -1;
            try
            {


                string sqlString = string.Format(@"
                INSERT INTO TBRoomPrice
                (
                        RoomID
                        ,RoomTime
                        ,Price
                        ,OneDealerPrice
                        ,TwoDealerPrice
                        ,ThreeDealerPrice
                        ,SortID
                        ,IsDeleted
                        ,Created
                        ,CreateTime
                        ,Updated
                        ,UpdateTime
                        ,Breakfast
                        ,HousingState
                        ,BedState
                        ,RoomQuantity
                        ,ScheduledRoomQuantity
                        ,LimitedSale 
                        ,LowPrice
                )
                VALUES
                (
                        @RoomID
                       ,@RoomTime
                       ,@Price
                       ,@OneDealerPrice
                       ,@TwoDealerPrice
                       ,@ThreeDealerPrice
                       ,@SortID
                       ,@IsDeleted
                       ,@Created
                       ,@CreateTime
                       ,@Updated
                       ,@UpdateTime
                       ,@Breakfast
                       ,@HousingState
                       ,@BedState
                       ,@RoomQuantity
                       ,@ScheduledRoomQuantity
                       ,@LimitedSale
                       ,@LowPrice
                )SELECT @@IDENTITY");

                DbCommand ldcDBCommand = base.GetCommand(sqlString);

                //参数设定
                SetParameter(ldcDBCommand, "@RoomID", DbType.Int32, model.RoomID);
                SetParameter(ldcDBCommand, "@RoomTime", DbType.DateTime, model.RoomTime);
                SetParameter(ldcDBCommand, "@Price", DbType.Decimal, model.Price);
                SetParameter(ldcDBCommand, "@OneDealerPrice", DbType.Decimal, model.OneDealerPrice);
                SetParameter(ldcDBCommand, "@TwoDealerPrice", DbType.Decimal, model.TwoDealerPrice);
                SetParameter(ldcDBCommand, "@ThreeDealerPrice", DbType.Decimal, model.ThreeDealerPrice);
                SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);
                SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
                SetParameter(ldcDBCommand, "@Created", DbType.Int32, model.Created);
                SetParameter(ldcDBCommand, "@CreateTime", DbType.DateTime, model.CreateTime);
                SetParameter(ldcDBCommand, "@Updated", DbType.Int32, model.Updated);
                SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);
                SetParameter(ldcDBCommand, "@Breakfast", DbType.Int32, model.Breakfast);
                SetParameter(ldcDBCommand, "@HousingState", DbType.Int32, model.HousingState);
                SetParameter(ldcDBCommand, "@BedState", DbType.Int32, model.BedState);
                SetParameter(ldcDBCommand, "@RoomQuantity", DbType.Int32, model.RoomQuantity);
                SetParameter(ldcDBCommand, "@ScheduledRoomQuantity", DbType.Int32, model.ScheduledRoomQuantity);
                SetParameter(ldcDBCommand, "@LimitedSale", DbType.Int32, model.LimitedSale); //限量售卖
                SetParameter(ldcDBCommand, "@LowPrice", DbType.Decimal, model.LowPrice);
                identityId = base.ExecuteScalarInsert(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return identityId;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(RoomPriceModel model)
        {
            int iTmp = -1;
            try
            {

                string sqlString = string.Format(@"
                UPDATE TBRoomPrice
                SET
                    RoomID = @RoomID
                    ,RoomTime = @RoomTime
                    ,Price = @Price
                    ,OneDealerPrice = @OneDealerPrice
                    ,TwoDealerPrice = @TwoDealerPrice
                    ,ThreeDealerPrice = @ThreeDealerPrice
                    ,SortID = @SortID
                    ,IsDeleted = @IsDeleted
                    ,Created = @Created
                    ,Updated = @Updated
                    ,UpdateTime = @UpdateTime
                    ,Breakfast = @Breakfast
                    ,HousingState=@HousingState
                    ,BedState=@BedState
                    ,RoomQuantity=@RoomQuantity
                    ,ScheduledRoomQuantity=@ScheduledRoomQuantity
                    ,LimitedSale = @LimitedSale
                    ,LowPrice = @LowPrice
                WHERE
                    ID = @ID
            ");

                DbCommand ldcDBCommand = base.GetCommand(sqlString);
                //绑定SQL参数
                SetParameter(ldcDBCommand, "@ID", DbType.Int32, model.ID);
                SetParameter(ldcDBCommand, "@RoomID", DbType.Int32, model.RoomID);
                SetParameter(ldcDBCommand, "@RoomTime", DbType.DateTime, model.RoomTime);
                SetParameter(ldcDBCommand, "@Price", DbType.String, model.Price);
                SetParameter(ldcDBCommand, "@OneDealerPrice", DbType.String, model.OneDealerPrice);
                SetParameter(ldcDBCommand, "@TwoDealerPrice", DbType.String, model.TwoDealerPrice);
                SetParameter(ldcDBCommand, "@ThreeDealerPrice", DbType.String, model.ThreeDealerPrice);
                SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);
                SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
                SetParameter(ldcDBCommand, "@Created", DbType.Int32, model.Created);
                SetParameter(ldcDBCommand, "@Updated", DbType.Int32, model.Updated);
                SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);
                SetParameter(ldcDBCommand, "@Breakfast", DbType.Int32, model.Breakfast);
                SetParameter(ldcDBCommand, "@HousingState", DbType.Int32, model.HousingState);
                SetParameter(ldcDBCommand, "@BedState", DbType.Int32, model.BedState);
                SetParameter(ldcDBCommand, "@RoomQuantity", DbType.Int32, model.RoomQuantity);
                SetParameter(ldcDBCommand, "@ScheduledRoomQuantity", DbType.Int32, model.ScheduledRoomQuantity);
                SetParameter(ldcDBCommand, "@LimitedSale", DbType.Int32, model.LimitedSale); //限量售卖
                SetParameter(ldcDBCommand, "@LowPrice", DbType.Decimal, model.LowPrice);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }


        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateRoomIdAndRoomTime(RoomPriceModel model)
        {
            int iTmp = -1;
            try
            {

                string sqlString = string.Format(@"
                UPDATE TBRoomPrice
                SET
                    Price = @Price
                    ,OneDealerPrice = @OneDealerPrice
                    ,TwoDealerPrice = @TwoDealerPrice
                    ,ThreeDealerPrice = @ThreeDealerPrice
                    ,SortID = @SortID
                    ,IsDeleted = @IsDeleted
                    ,Created = @Created
                    ,Updated = @Updated
                    ,UpdateTime = @UpdateTime
                    ,Breakfast = @Breakfast
                    ,LimitedSale = @LimitedSale 
                    ,LowPrice = @LowPrice
                WHERE
                    RoomID = @RoomID and RoomTime = @RoomTime
            ");

                DbCommand ldcDBCommand = base.GetCommand(sqlString);
                //绑定SQL参数
                SetParameter(ldcDBCommand, "@RoomID", DbType.Int32, model.RoomID);
                SetParameter(ldcDBCommand, "@RoomTime", DbType.DateTime, model.RoomTime);
                SetParameter(ldcDBCommand, "@Price", DbType.String, model.Price);
                SetParameter(ldcDBCommand, "@OneDealerPrice", DbType.String, model.OneDealerPrice);
                SetParameter(ldcDBCommand, "@TwoDealerPrice", DbType.String, model.TwoDealerPrice);
                SetParameter(ldcDBCommand, "@ThreeDealerPrice", DbType.String, model.ThreeDealerPrice);
                SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);
                SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
                SetParameter(ldcDBCommand, "@Created", DbType.Int32, model.Created);
                SetParameter(ldcDBCommand, "@Updated", DbType.Int32, model.Updated);
                SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);
                SetParameter(ldcDBCommand, "@Breakfast", DbType.Int32, model.Breakfast);
                SetParameter(ldcDBCommand, "@LimitedSale", DbType.Int32, model.LimitedSale);//限量售卖 
                SetParameter(ldcDBCommand, "@LowPrice", DbType.Decimal, model.LowPrice);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateRoomStatusAndRoomTime(RoomPriceModel model)
        {
            int iTmp = -1;
            try
            {

                string sqlString = string.Format(@"
                UPDATE TBRoomPrice
                SET
                    IsDeleted = @IsDeleted
                    ,Created = @Created
                    ,Updated = @Updated
                    ,UpdateTime = @UpdateTime
                    ,HousingState=@HousingState
                    ,BedState=@BedState
                    ,RoomQuantity=@RoomQuantity
                    ,ScheduledRoomQuantity=@ScheduledRoomQuantity
                    ,LowPrice = @LowPrice
                    ,LimitedSale = @LimitedSale
                WHERE
                    RoomID = @RoomID and RoomTime = @RoomTime
            ");

                DbCommand ldcDBCommand = base.GetCommand(sqlString);
                //绑定SQL参数
                SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
                SetParameter(ldcDBCommand, "@Created", DbType.Int32, model.Created);
                SetParameter(ldcDBCommand, "@Updated", DbType.Int32, model.Updated);
                SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);
                SetParameter(ldcDBCommand, "@HousingState", DbType.Int32, model.HousingState);
                SetParameter(ldcDBCommand, "@BedState", DbType.Int32, model.BedState);
                SetParameter(ldcDBCommand, "@RoomQuantity", DbType.Int32, model.RoomQuantity);
                SetParameter(ldcDBCommand, "@ScheduledRoomQuantity", DbType.Int32, model.ScheduledRoomQuantity);
                SetParameter(ldcDBCommand, "@RoomID", DbType.Int32, model.RoomID);
                SetParameter(ldcDBCommand, "@RoomTime", DbType.DateTime, model.RoomTime);
                SetParameter(ldcDBCommand, "@LowPrice", DbType.Decimal, model.LowPrice);
                SetParameter(ldcDBCommand, "@LimitedSale", DbType.Int32, model.LimitedSale);
                
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }


        /// <summary>
        /// 单个删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(int id)
        {
            int iTmp = -1;
            try
            {
                StringBuilder updateSql = new StringBuilder();
                updateSql.Append("DELETE FROM TBRoomPrice");
                updateSql.Append(" WHERE ");
                updateSql.Append(" ID=@ID ");
                DbCommand ldcDBCommand = base.GetCommand(updateSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.Int32, id);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="idList"></param>
        /// <returns></returns>
        public int DeleteList(string ids)
        {
            int iTmp = -1;
            try
            {
                StringBuilder updateSql = new StringBuilder();
                updateSql.Append("exec('DELETE FROM TBRoomPrice");
                updateSql.Append(" WHERE ");
                updateSql.Append(" ID in ('+@ID+') ')");
                DbCommand ldcDBCommand = base.GetCommand(updateSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.String, ids);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }
    }
}

